package com.jxc.jxc01.yk.dao;


import com.jxc.jxc01.yk.entity.Customer;
import com.jxc.jxc01.yk.entity.PageHelper;
import com.jxc.jxc01.yk.util.JDBCConn;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CustomerDaoImpl implements ICustomerDao {


    /**
     * 增加客户信息
     */
    public boolean addCustomer(Customer customer) {
        //将查询到的销售清单信息存储集合中
        List<Customer> customers = new ArrayList<>();
        ResultSet rs = null;
        try {

            //3.插入表
            String sql = "insert into customerinformation(customername,phone,address,mail,fax,createcustomer_time,bank) " +
                    "values (?,?,?,?,?,?,?)";
            PreparedStatement pstmt = JDBCConn.conn(sql);
            pstmt.setString(1, customer.getCustomername());
            pstmt.setString(2, customer.getPhone());
            pstmt.setString(3, customer.getAddress());
            pstmt.setString(4, customer.getMail());
            pstmt.setInt(5, customer.getFax());
            pstmt.setString(6, customer.getCreatecustomer_time());
            pstmt.setString(7, customer.getBank());
            int rows = pstmt.executeUpdate();
            if (rows == 0) {
                //添加失败
                return false;
            }
        } catch (  SQLException e) {
            e.printStackTrace();
        }
        return true;
    }


    /**
     * 查询所有客户信息
     */
    public List<Customer> findCustomerList(PageHelper<Customer> pageHelper) {
        //将查询到的销售清单信息存储集合中
        List<Customer> customers = new ArrayList<>();
        ResultSet rs = null;
        try {
        //分页查询
        String sql = "select count(*) total from customerinformation";
        PreparedStatement pstmt = JDBCConn.conn(sql);
        rs = pstmt.executeQuery();
        //列表数据总数
        while (rs.next()){
            int total = rs.getInt("total");
            pageHelper.setTotal(total);
        }
        //总页数
        pageHelper.setPageCount(pageHelper.getTotal()%pageHelper.getPageSize()==0?
                pageHelper.getTotal()/pageHelper.getPageSize():pageHelper.getTotal()/pageHelper.getPageSize()+1);
        //3.查询销售表
        sql = "select * from customerinformation limit ?,?";
        pstmt = JDBCConn.conn(sql);
        pstmt.setInt(1, (pageHelper.getPageNum()-1)*pageHelper.getPageSize());
        pstmt.setInt(2, pageHelper.getPageSize());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Customer customer = new Customer();
                customer.setId(rs.getInt(1));
                customer.setCustomername(rs.getString(2));
                customer.setPhone(rs.getString(3));
                customer.setAddress(rs.getString(4));
                customer.setMail(rs.getString(5));
                customer.setFax(rs.getInt(6));
                customer.setCreatecustomer_time(rs.getString(7));
                customer.setBank(rs.getString(8));
                customers.add(customer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return customers;
    }

    /**
     * 根据客户姓名查找某一条记录
     */
    public List<Customer> findOneOfCustomer(String customername) {
        //将查询到的销售清单信息存储集合中
        List<Customer> customers = new ArrayList<>();
        ResultSet rs = null;
        try {
            //3.查询销售表
            String sql = "select * from customerinformation where customername like ?";
            PreparedStatement pstmt = JDBCConn.conn(sql);
            pstmt.setString(1, "%" + customername + "%");
            pstmt.executeQuery();
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Customer customer = new Customer();
                customer.setId(rs.getInt(1));
                customer.setCustomername(rs.getString(2));
                customer.setPhone(rs.getString(3));
                customer.setAddress(rs.getString(4));
                customer.setMail(rs.getString(5));
                customer.setFax(rs.getInt(6));
                customer.setCreatecustomer_time(rs.getString(7));
                customer.setBank(rs.getString(8));
                customers.add(customer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return customers;
    }

    /**
     * 删除客户信息
     */
    public boolean deleteCustomer(int id) {
        try {
            //3.查询表
            String sql = "delete from customerinformation where id=?";
            PreparedStatement pstmt = JDBCConn.conn(sql);
            pstmt.setInt(1, id);
            int rows = pstmt.executeUpdate();
            if (rows == 0) {
                //删除失败
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return true;
    }

    /**
     * 修改客户信息
     */
    public boolean updateCustomer(Customer customer) {
        ResultSet rs = null;
        try {
            //3.修改表
            String sql = "update customerinformation set customername=?,phone=?,address=?," +
                    "mail=?,fax=?,createcustomer_time=?,bank=? where id=?";
            PreparedStatement pstmt = JDBCConn.conn(sql);
            pstmt.setString(1, customer.getCustomername());
            pstmt.setString(2, customer.getPhone());
            pstmt.setString(3, customer.getAddress());
            pstmt.setString(4, customer.getMail());
            pstmt.setInt(5, customer.getFax());
            pstmt.setString(6, customer.getCreatecustomer_time());
            pstmt.setString(7, customer.getBank());
            pstmt.setInt(8, customer.getId());
            int rows = pstmt.executeUpdate();
            if (rows == 0) {
                //修改失败
                return false;
            }
        }catch (SQLException e) {
           e.printStackTrace();
        }
        return true;
    }
}
